"""
线损模块的数据库操作
"""
from datetime import date
from decimal import Decimal
from typing import List, Optional, Tuple

from app.schemas.line_loss_schemas import RtuLineLossListRequest
from common.database import DatabaseService
from common.exceptions import BizException, ErrorCode
from common.logger import create_logger

logger = create_logger("line_loss_crud")


async def get_rtu_line_loss_list(
		db: DatabaseService,
		request: RtuLineLossListRequest,
		page: int,
		size: int
) -> Tuple[int, List[dict]]:
	"""
	分页获取RTU线损列表

	Args:
		db: 数据库服务实例
		request: RTU线损查询请求参数
		page: 页码
		size: 每页大小

	Returns:
		Tuple[int, List[dict]]: 总数和数据列表
	"""
	# 构建WHERE条件
	where_conditions = ["1=1"]
	params: List = []

	if request.start_date and request.end_date and request.start_date <= request.end_date:
		where_conditions.append("ll.calc_date BETWEEN %s AND %s")
		params.append(request.start_date)
		params.append(request.end_date)

	if request.serial_no:
		where_conditions.append("r.serial_no = %s")
		params.append(request.serial_no)

	if request.station_id:
		where_conditions.append("r.station_id = %s")
		params.append(request.station_id)

	where_clause = " AND ".join(where_conditions)

	# 查询总数
	count_sql = f"""
        SELECT COUNT(DISTINCT ll.id) as total
        FROM dev_rtu_line_loss ll
        INNER JOIN dev_rtu r ON ll.rtu_id = r.id
        WHERE {where_clause}
    """

	try:
		count_row = db.get_one(count_sql, params)
		total = count_row["total"] if count_row else 0

		if total == 0:
			return 0, []

		# 查询数据
		offset = (page - 1) * size
		data_sql = f"""
            SELECT
                ll.rtu_id,
                r.serial_no,
                b.serial_no as box_serial_no,
                s.serial_no as station_serial_no,
                ll.loss_rate,
                ll.loss_kwh,
                ll.rtu_consumption,
                ll.meters_consumption,
                ll.meter_count,
                ll.valid_meter_count,
                ll.abnormal_flag,
                ll.trend,
                ll.calc_date
            FROM dev_rtu_line_loss ll
            INNER JOIN dev_rtu r ON ll.rtu_id = r.id
            LEFT JOIN dev_box b ON r.box_id = b.id
            LEFT JOIN dev_station s ON r.station_id = s.id
            WHERE  {where_clause}
            ORDER BY ll.calc_date DESC, ll.loss_rate DESC
            LIMIT %s OFFSET %s
        """
		params.extend([size, offset])

		rows = db.get_all(data_sql, params)
		return total, rows

	except Exception as e:
		logger.exception("查询RTU线损列表失败: %s", e)
		raise BizException.from_error_code(
			ErrorCode.DB_QUERY_ERROR,
			message="查询RTU线损列表失败"
		) from e


async def get_rtu_line_loss_detail(
		db: DatabaseService,
		rtu_id: int,
		calc_date: date
) -> Optional[dict]:
	"""
	获取RTU线损详情
	"""
	sql = """
        SELECT
            ll.rtu_id,
            r.serial_no,
            ll.calc_date,
            ll.loss_rate,
            ll.loss_kwh,
            ll.rtu_consumption,
            ll.meters_consumption,
            ll.meter_count,
            ll.valid_meter_count
        FROM dev_rtu_line_loss ll
        INNER JOIN dev_rtu r ON ll.rtu_id = r.id
        WHERE ll.rtu_id = %s AND ll.calc_date = %s
    """

	try:
		row = db.get_one(sql, (rtu_id, calc_date))
		return row
	except Exception as e:
		logger.exception("查询RTU线损详情失败: %s", e)
		raise BizException.from_error_code(
			ErrorCode.DB_QUERY_ERROR,
			message="查询RTU线损详情失败"
		) from e


async def get_meters_consumption_by_rtu(
		db: DatabaseService,
		rtu_id: int,
		calc_date: date
) -> List[dict]:
	"""
	获取RTU下所有电表的用电量明细（从DC_METER_FREEZE表）

	Args:
		db: 数据库服务实例
		rtu_id: RTU设备ID
		calc_date: 计算日期

	Returns:
		List[dict]: 电表用电量明细列表
	"""
	sql = """
        SELECT
            m.id as meter_id,
            m.serial_no,
            m.status,
            COALESCE(f.daily_kwh, 0) as daily_consumption,
            CASE WHEN f.daily_kwh IS NOT NULL AND f.daily_kwh > 0 THEN 1 ELSE 0 END as has_data
        FROM dev_meter m
        LEFT JOIN dc_meter_freeze f ON m.serial_no = f.serial_no
            AND f.freeze_day = %s
        WHERE m.rtu_id = %s AND m.deleted = 0
        ORDER BY m.id
    """

	try:
		rows = db.get_all(sql, (calc_date, rtu_id))
		return rows
	except Exception as e:
		logger.exception("查询电表用电量明细失败: %s", e)
		raise BizException.from_error_code(
			ErrorCode.DB_QUERY_ERROR,
			message="查询电表用电量明细失败"
		) from e


async def get_line_loss_overview(
		db: DatabaseService,
		calc_date: Optional[date] = None,
		station_id: Optional[int] = None
) -> dict:
	"""
	获取线损概览数据

	Args:
		db: 数据库服务实例
		calc_date: 计算日期，为None时查询所有历史数据
		station_id: 台区ID，用于过滤特定台区的数据

	Returns:
		dict: 包含 total_loss_rate 和 total_loss_kwh
	"""
	# 构建 WHERE条件
	where_clauses = []
	params = []

	if calc_date is not None:
		where_clauses.append("ll.calc_date = %s")
		params.append(calc_date)

	if station_id is not None:
		where_clauses.append("r.station_id = %s")
		params.append(station_id)

	where_clause = "WHERE " + " AND ".join(where_clauses) if where_clauses else ""

	sql = f"""
        SELECT
            SUM(ll.loss_kwh) as total_loss_kwh,
            SUM(ll.rtu_consumption) as total_rtu_consumption
        FROM dev_rtu_line_loss ll
        INNER JOIN dev_rtu r ON ll.rtu_id = r.id
        {where_clause}
    """

	try:
		row = db.get_one(sql, tuple(params))
		if row and row["total_rtu_consumption"]:
			total_loss_kwh = Decimal(str(row["total_loss_kwh"] or 0))
			total_rtu_consumption = Decimal(str(row["total_rtu_consumption"]))
			total_loss_rate = (total_loss_kwh / total_rtu_consumption * 100) if total_rtu_consumption > 0 else Decimal(
				0)

			return {
				"total_loss_rate": round(total_loss_rate, 2),
				"total_loss_kwh": round(total_loss_kwh, 2)
			}

		return {
			"total_loss_rate": Decimal(0),
			"total_loss_kwh": Decimal(0)
		}
	except Exception as e:
		logger.exception("查询线损概览数据失败: %s", e)
		raise BizException.from_error_code(
			ErrorCode.DB_QUERY_ERROR,
			message="查询线损概览数据失败"
		) from e


async def get_rtu_consumption_by_date(
		db: DatabaseService,
		rtu_code: str,
		calc_date: date
) -> Optional[Decimal]:
	"""
	获取RTU当日用电量（从DC_METER_FREEZE表）

	Args:
		db: 数据库服务实例
		rtu_code: RTU设备编码
		calc_date: 计算日期

	Returns:
		Optional[Decimal]: RTU用电量，无数据返回None
	"""
	sql = """
        SELECT daily_kwh
        FROM dc_meter_freeze
        WHERE serial_no = %s
          AND freeze_day = %s
    """

	try:
		row = db.get_one(sql, (rtu_code, calc_date))
		if not row or row["daily_kwh"] is None:
			return None
		return Decimal(str(row["daily_kwh"]))
	except Exception as e:
		logger.exception("查询RTU用电量失败: %s", e)
		raise BizException.from_error_code(
			ErrorCode.DB_QUERY_ERROR,
			message="查询RTU用电量失败"
		) from e


async def get_meters_consumption_by_rtu_date(
		db: DatabaseService,
		rtu_id: int,
		calc_date: date
) -> List[dict]:
	"""
	获取RTU下所有在线电表的用电量（从DC_METER_FREEZE表）

	Args:
		db: 数据库服务实例
		rtu_id: RTU设备ID
		calc_date: 计算日期

	Returns:
		List[dict]: 电表用电量列表
	"""
	sql = """
        SELECT
            m.id,
            m.serial_no,
            m.status,
            f.daily_kwh
        FROM dev_meter m
        LEFT JOIN dc_meter_freeze f ON m.serial_no = f.serial_no
            AND f.freeze_day = %s
        WHERE m.rtu_id = %s
          AND m.deleted = 0
          AND m.status = 'ONLINE'
    """

	try:
		rows = db.get_all(sql, (calc_date, rtu_id))
		return rows
	except Exception as e:
		logger.exception("查询电表用电量失败: %s", e)
		raise BizException.from_error_code(
			ErrorCode.DB_QUERY_ERROR,
			message="查询电表用电量失败"
		) from e


async def create_rtu_line_loss(
		db: DatabaseService,
		rtu_id: int,
		rtu_code: str,
		calc_date: date,
		rtu_consumption: Decimal,
		meters_consumption: Decimal,
		loss_kwh: Decimal,
		loss_rate: Decimal,
		meter_count: int,
		valid_meter_count: int,
		data_quality: str = "NORMAL"
) -> int:
	"""
	创建RTU线损记录
	"""
	sql = """
        INSERT INTO dev_rtu_line_loss (
            calc_date, rtu_id, rtu_code, rtu_consumption, meters_consumption,
            loss_kwh, loss_rate, meter_count, valid_meter_count, data_quality
        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE
            rtu_consumption = VALUES(rtu_consumption),
            meters_consumption = VALUES(meters_consumption),
            loss_kwh = VALUES(loss_kwh),
            loss_rate = VALUES(loss_rate),
            meter_count = VALUES(meter_count),
            valid_meter_count = VALUES(valid_meter_count),
            data_quality = VALUES(data_quality)
    """

	try:
		result = db.insert(
			sql,
			(calc_date, rtu_id, rtu_code, rtu_consumption, meters_consumption,
			 loss_kwh, loss_rate, meter_count, valid_meter_count, data_quality)
		)
		return result
	except Exception as e:
		logger.exception("创建RTU线损记录失败: %s", e)
		raise BizException.from_error_code(
			ErrorCode.DATABASE_ERROR,
			message="创建RTU线损记录失败"
		) from e
